[Ultimate Guide] Using Goal Seek in Excel (With Examples) |
您所在的位置：网站首页 › Excel goal seek › [Ultimate Guide] Using Goal Seek in Excel (With Examples) |
[Ultimate Guide] Using Goal Seek in Excel (With Examples)
|
The Goal Seek tool can be used to determine an unknown numerical figure to seek a particular goal number. Microsoft Excel can be used for analyzing highly utilitarian metrics like demand and sales forecasting, predicting, mathematical calculations, and more. Steps to use Goal Seek in ExcelLet鈥檚 get started with this ultimate step-by-step guide to using the Goal seek option correctly for achieving different objectives.  Sales Data for Four Products
For instance, you have a set of sales data for 3 months of four products. Every month you reach your minimum sales goal of 75% for all four products collectively. In the third month, you add a fifth product to your store and want to predict the number of units to be sold in a month to achieve the minimum 75% sales. We use goal seek to predict this data. Applying Goal Seek for prediction Total Percentage for 5 Products
First, find the percentage sales of all five products even though the fifth product column will be empty (refer to the image).Our current result is 62.42% of total sales as there is no entry in the fifth product.Now, go to the Data tab, under the Forecast section pull down What-if Analysis.Select Goal Seek and a new window pop up showing Goal Seek settings.In the Set cell box, select the cell with total sales percentage for the 3rd month (calculated in Point 1).In the To value box enter the goal sales percent you are looking i.e., 75.By changing the cell box, select the empty cell i.e., fifth product, within the same row.Press OK.
 Goal Seek Options
You now get a random predicted figure that will be required to achieve a minimum of 75% sales per month keeping all others figures the same.  Sales determined for fifth product via Goal Seek
Limitations: Note that you would require to determine manually for every unknown figure every time you perform a Goal Seek in excel. Dragging the cell data upward or downward will not predict the right figures for you. Also, Goal Seek can be performed to find out only one unknown figure of a row or column in a database. More Examples of Goal SeekGoal Seek in Excel can be used to find lost, uncalculated or predictable data in a database. For example, in the COVID-19 era, your school runs examinations for 6 subjects. But examinations of only 5 subjects were given by all students after which a lockdown was imposed. You are now supposed to reckon a predicted mark for students in the 6th subject. Here is how Goal Seek can help-  Average Student and Nerd Student
There are two types of students here- an average and a nerd. You have to predict a figure for a mark lost based on the marks obtained in other 5 subjects, for the 6th subject. Let鈥檚 begin-  Finding Total Percentage
Find a total percentage for all cells with and without marks for both students.Also, find a percentage for 5 subjects for both students to understand their performances in the attempted tests solely.
This figure will help you determine a number in Goal seek settings to set as the goal TOTAL to be achieved to predict a mark for the 6th subject.  Total Percentage for 5 Subjects
Go to the Data tab, under the Forecast section, pull down on What-if Analysis.Select Goal Seek which opens a new window. 聽
 Finding Marks for 6th Subject
In the Set cell box, select the cell with a Total percentage for 6 subjects.In To value, enter the total percentage you found for 5 subjects.By changing cells, select the empty cell without marks i.e., the marks of the 6th subject.Hit OK. You now have the predicted marks with you.
 Mark Predicted for 6th Subject
This is how Goal Seek in Excel can help you in different ways to analyze different types of data. ConclusionThis article was a detailed guide on Goal Seek in Excel and how it could be used in different scenarios. If you have any questions, drop a comment below and we will answer them! |
【本文地址】